﻿Imports System.Data
Imports System.Data.SqlClient
Public Class MarcasDAL
    Public Sub New()
        'contrutor padrão
    End Sub
    Dim cmd As SqlCommand
    Dim objD As Dados
#Region "Métodos"
    Public Sub inserir(mar_nome As String)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "insert into marcas(mar_nome, mar_ativo_sn) values (@mar_nome, 'S')"
            cmd.Parameters.Add(New SqlParameter("@mar_nome", SqlDbType.VarChar)).Value = mar_nome
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Sub alterar(mar_nome As String, mar_codigo As Integer)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "update marcas set mar_nome = @mar_nome where mar_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@mar_nome", SqlDbType.VarChar)).Value = mar_nome
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = mar_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Sub excluir(mar_codigo As Integer)
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "update marcas set mar_ativo_sn='N' where mar_codigo = @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = mar_codigo
            objD.executacomando(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
    End Sub
    Public Function localizar(codigo As Integer) As DataTable
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            objD = New Dados
            cmd.CommandText = "select * from marcas where mar_codigo = @codigo and mar_ativo_sn<>'N'"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
    End Function
    Function localizar(ByVal descricao As String) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "ultimo") Then
                cmd.CommandText = "SELECT mar_codigo = max(mar_codigo) FROM marcas where mar_ativo_sn<>'N' "
            ElseIf (descricao = "primeiro") Then
                cmd.CommandText = "SELECT mar_codigo = min(mar_codigo) FROM marcas where mar_ativo_sn<>'N' "
            Else
                'Comando para localizar vários registros no banco
                cmd.CommandText = "SELECT * FROM marcas WHERE mar_nome like @descricao and mar_ativo_sn<>'N'"
                'Parâmetro de entrada
                cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" + descricao + "%"
            End If
            'Execução do comando
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        'tab = Nothing
    End Function

    Function localizarProxAnterior(ByVal descricao As String, ByVal codigo As Integer) As DataTable
        Dim cmd As New SqlCommand
        Dim objD As New Dados
        Dim tab As DataTable = Nothing
        Try
            If (descricao = "proximo") Then
                cmd.CommandText = "SELECT mar_codigo FROM marcas WHERE mar_codigo = (SELECT MIN(mar_codigo) FROM marcas WHERE mar_codigo > @codigo) and mar_ativo_sn<>'N'"

            ElseIf (descricao = "anterior") Then
                cmd.CommandText = "SELECT mar_codigo FROM marcas  WHERE mar_codigo = (SELECT MAX(mar_codigo) FROM marcas WHERE mar_codigo < @codigo) and mar_ativo_sn<>'N'"
            End If
            'Parâmetro de entrada
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            'Execução do comando
            tab = objD.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        End Try
        cmd = Nothing
        objD = Nothing
        Return tab
        'tab = Nothing

    End Function
#End Region
End Class
